#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
全国完整行政区划SQL生成脚本
作者：Wu.Liang
日期：2025-06-27
功能：递归解析pcas-code.json，分省生成全国完整行政区划SQL，每省一个SQL文件，并生成总调度SQL
"""

import json
import os
from datetime import datetime

# 路径配置
DATA_FILE = "AI_Task/区域管理测试数据补充_执行文档/china_division_raw.json"
OUTPUT_DIR = "AI_Task/区域管理测试数据补充_执行文档/province_sqls"
ALL_SQL_FILE = "AI_Task/区域管理测试数据补充_执行文档/05_全国完整行政区划总调度.sql"

# sc_region表字段模板
SQL_HEADER = '''INSERT INTO `sc_region` (
  `id`, `region_name`, `region_code`, `parent_id`, `region_type`, `level`,
  `manager_id`, `description`, `address`, `longitude`, `latitude`, `area`,
  `population`, `community_count`, `contact_phone`, `contact_email`, `postal_code`,
  `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`,
  `deleted`, `version`, `region_level`
) VALUES\n'''

# 递归解析行政区划数据，生成SQL

def parse_region(code, node, parent_id, level, region_type, values):
    name = node['name']
    value = f"('{code}', '{name}', '{code}', '{parent_id}', {region_type}, {level}, NULL, '{name}', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, NULL, 1, 0, 1, NOW(), NULL, NOW(), 0, 1, {level})"
    values.append(value)
    # 递归子节点
    if 'children' in node and node['children']:
        next_level = level + 1
        next_type = region_type + 1 if region_type < 4 else 4
        for sub_node in node['children']:
            parse_region(sub_node['code'], sub_node, code, next_level, next_type, values)

def main():
    print("=" * 60)
    print("全国完整行政区划SQL生成工具")
    print("作者：Wu.Liang")
    print("日期：", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    print("=" * 60)
    # 读取数据
    with open(DATA_FILE, 'r', encoding='utf-8') as f:
        data = json.load(f)
    # 顶级为'86'，下为31个省份
    provinces = data['86']
    if not os.path.exists(OUTPUT_DIR):
        os.makedirs(OUTPUT_DIR)
    all_sql_files = []
    for prov_code, prov_node in provinces.items():
        prov_name = prov_node['name']
        values = []
        parse_region(prov_code, prov_node, 0, 1, 1, values)
        sql_file = os.path.join(OUTPUT_DIR, f"province_{prov_code}_{prov_name}.sql")
        with open(sql_file, 'w', encoding='utf-8') as f:
            f.write(f"-- {prov_name} 行政区划SQL\n-- 作者：Wu.Liang\n-- 日期：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n")
            f.write("TRUNCATE TABLE `sc_region`;\n\n")
            f.write(SQL_HEADER)
            f.write(",\n".join(values) + ";\n")
        all_sql_files.append(sql_file)
        print(f"已生成：{sql_file}，共{len(values)}条")
    # 生成总调度SQL
    with open(ALL_SQL_FILE, 'w', encoding='utf-8') as f:
        f.write("-- 全国完整行政区划总调度SQL\n-- 作者：Wu.Liang\n-- 日期：{}\n\n".format(datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
        for sql_file in all_sql_files:
            f.write(f"source {sql_file};\n")
    print(f"\n总调度SQL已生成：{ALL_SQL_FILE}")
    print("全部省份SQL已生成，任务完成！")

if __name__ == "__main__":
    main() 